Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# PL/SQL Scripts Used in Migrating to ASM Storage

The following PL/SQL scripts perform tasks which arise in the migration scenarios described in this chapter.

## Generating ASM-to-Non-ASM Storage Migration Script

You can use the following PL/SQL script to generate a series of RMAN commands that you can use to migrate your database back from ASM to non-ASM disk storage.

    set serveroutput on;
    declare
        cursor df is select file#, name from v$datafile;
    begin
        dbms_output.put_line('run');
        dbms_output.put_line('{');
        for dfrec in df loop
            dbms_output.put_line('set newname for datafile ' ||
                dfrec.file# || ' to ''' || dfrec.name ||''' ;');
        end loop;
        dbms_output.put_line('restore database;');
        dbms_output.put_line('switch all;');
        dbms_output.put_line('}');
    end;

Run this PL/SQL script and save the output into a file. The result is an RMAN script which you can save to a file and later run as a command file in the RMAN client to migrate your datafiles back out of ASM storage to their original non-ASM locations. Even if you later add or delete datafiles, this script provides a useful starting point for a migration script that will work for the new database.

## Migrating Online Logs of Primary Database to ASM

The following PL/SQL script can be used to migrate the online redo log groups into ASM, as part of migrating a database or a flash recovery area into ASM. For each online redo log group, the script adds a log file stored in ASM, archives the current redo logs, and then drops the non-ASM log file.

    declare
       cursor rlc is
          select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
            from v$log
          union
          select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
            from v$standby_log
          order by 1;
       stmt     varchar2(2048);
       swtstmt  varchar2(1024) := 'alter system switch logfile';
       ckpstmt  varchar2(1024) := 'alter system checkpoint global';
    begin
       for rlcRec in rlc loop
          if (rlcRec.srl = 'YES') then
             stmt := 'alter database add standby logfile thread ' ||
                     rlcRec.thr || ' ''+DISK'' size ' ||
                     rlcRec.bytes_k || 'K';
             execute immediate stmt;
             stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
             execute immediate stmt;
          else
             stmt := 'alter database add logfile thread ' ||
                     rlcRec.thr || ' ''+DISK'' size ' ||
                     rlcRec.bytes_k || 'K';
             execute immediate stmt;
             begin
                stmt := 'alter database drop logfile group ' || rlcRec.grp;
                dbms_output.put_line(stmt);
                execute immediate stmt;
             exception
                when others then
                   execute immediate swtstmt;
                   execute immediate ckpstmt;
                   execute immediate stmt;
             end;
          end if;
       end loop;
    end;